#coding=utf-8
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

from pymongo import MongoClient
import os

MAX_ROW = 60000

def deep_search(target_data, dir):
    names = os.listdir(dir)

    for name in names:
        if name == '.DS_Store':
            continue
        path = os.path.join(dir, name)
        if os.path.isdir(path):
            deep_search(target_data, path)
        else:
            new_name = name.rstrip('.txt')
            sub_paths = path.split('/劳动争议')
            new_path = '/劳动争议'+sub_paths[-1]
            target_data.append({'_id':new_name, 'path':new_path})



def import_cases(dir):

    target_data = []
    print '读取数据中......'
    deep_search(target_data,dir)
    print '读取成功, 导入中......'
    client = MongoClient(host='127.0.0.1', port=27017)
    db = client['CASE']
    table = db['case_paths']
    for element in target_data:
        try:
            table.update({'_id':element['_id']}, {'$set':{'path':element['path']}}, upsert=True)
        except:
            print element['_id'], element['path']
    print '导入成功！'
    client.close()
def generate_excel():
    client = MongoClient(host='127.0.0.1', port=27017)
    db = client['CASE']
    table = db['case_paths']
    print '读取case_paths表......'
    recs_cursor = table.find()
    id2paths = {}
    for rec in recs_cursor:
        str = rec['path']
        path = u'D:' + str
        id = rec["_id"]
        id2paths[id] = path
    print '读取cases表......'
    import xlwt
    table = db['cases']
    # provinces = ["山东省", "河南省", "浙江省", "安徽省", "湖南省","河北省", "山西省", "福建省", "天津市", "陕西省", "新疆维吾尔自治区", "青海省", "宁夏回族自治区", "江西省", "云南省", "广西壮族自治区", "最高人民法院","贵州省", "内蒙古自治区", "海南省", "甘肃省", "上海市", "新疆维吾尔自治区高级人民法院生产建设兵团分院", "西藏自治区"]
    provinces = ["广东省", "湖北省", "北京市", "吉林省", "四川省", "江苏省", "重庆市", "黑龙江省", "辽宁省", "山东省", "河南省", "浙江省", "安徽省", "湖南省","河北省", "山西省", "福建省", "天津市", "陕西省", "新疆维吾尔自治区", "青海省", "宁夏回族自治区", "江西省", "云南省", "广西壮族自治区", "最高人民法院","贵州省", "内蒙古自治区", "海南省", "甘肃省", "上海市", "新疆维吾尔自治区高级人民法院生产建设兵团分院", "西藏自治区"]
    years = ['2016', '2017']

    for province in provinces:
        for year in years:
            file_name = province + year
            file_number = 1
            rows = 0
            print '开始处理%s......' % province
            recs_cursor = table.find({"province":province, "caseNo":{"$regex":year}})
            book = xlwt.Workbook(encoding='utf-8')
            sheet = book.add_sheet('劳动争议')
            keys = ['caseNo', 'wsName', 'judgeDate', 'pubdate', 'province', 'court', 'cause', 'caseType', 'process', 'basis',
                    'cause_id', 'relatedState', 'downState', 'fixCauseState', 'create_time']
            for index, rec in enumerate(recs_cursor):
                path = id2paths.get(rec['wsId'])
                if path is not None:
                    link = 'HYPERLINK("%s";"%s")' % (path, rec['wsId'])
                    sheet.write(rows, 0, xlwt.Formula(link))
                else:
                    sheet.write(rows, 0, rec['wsId'])
                for col, key in enumerate(keys):
                    sheet.write(rows, col+1, rec[key])

                rows += 1
                if rows == MAX_ROW:
                    book.save('%s%d.xls' % (file_name, file_number))
                    file_number += 1
                    rows = 0
                    book = xlwt.Workbook(encoding='utf-8')
                    sheet = book.add_sheet('劳动争议')
            if file_number > 1:
                book.save('%s%d.xls' % (file_name, file_number))
            else:
                book.save('%s.xls' % (file_name))
            client.close()
    print '写入完成-o-'





def test(skip=10, **kwargs):
    print kwargs




if __name__ == '__main__':
    # import_cases('/Users/shiqingshuai/Desktop/劳动争议')
    generate_excel()